﻿using Common;
using System;
using System.Collections.Generic;
using System.Text;
using SqliteDataAccess;
using SqliteDataAccess.SqliteDataManagerTableAdapters;
using OfficeOpenXml;
using System.IO;
using System.Diagnostics;

namespace Business
{
    public class LaporanTriwulanBusiness : BaseBusiness
    {

        public override int Load()
        {
            throw new NotImplementedException();
        }

        public override int Save()
        {
            LaporanTriwulanTableAdapter laporanTriwulanTA;
            try
            {
                laporanTriwulanTA = new LaporanTriwulanTableAdapter();
                return laporanTriwulanTA.Update(LppbopSqlite.LaporanTriwulan);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        public int LoadByTriwulan(String triwulan, int tahun)
        {
            int tri = (int)Enums.GetTriwulanByName(triwulan);
            LaporanTriwulanTableAdapter laporanTriwulanTA = new LaporanTriwulanTableAdapter();
            return laporanTriwulanTA.Fill(LppbopSqlite.LaporanTriwulan, tri, tahun);
        }

        public int InsertLaporanTriwulan(string triwulan, int tahun)
        {
            BukuKasUmumTableAdapter bukuKasUmumTA = new BukuKasUmumTableAdapter();
            int[] months = Enums.GetMonthNamesByTriwulan(triwulan);
            SqliteDataManager.BukuKasUmumDataTable bukuKasUmum = bukuKasUmumTA.GetDataByBulan(months[0], months[1], months[2], tahun);

            if (bukuKasUmum.Count == 0)
            {
                return 0;
            }
            else
            {
                long penerimaan = bukuKasUmum.TotalPenerimaan;
                long pengeluaran = bukuKasUmum.TotalPengeluaran;

                int triwulanId = (int)Enums.GetTriwulanByName(triwulan);
                LaporanTriwulanTableAdapter laporanTriwulanTA = new LaporanTriwulanTableAdapter();
                SqliteDataManager.LaporanTriwulanDataTable laporanTriwulan = laporanTriwulanTA.GetData(triwulanId, tahun);

                if (laporanTriwulan.Count == 0)
                {
                    SqliteDataManager.LaporanTriwulanRow newRow = laporanTriwulan.NewLaporanTriwulanRow();
                    newRow.triwulan = triwulanId;
                    newRow.tahun = tahun;
                    newRow.no = "1.";
                    newRow.uraian = string.Format("DANA BOP TRIWULAN {0}", triwulan);
                    newRow.prosentase_anggaran = penerimaan == 0 ? 0 : (double)pengeluaran / (double)penerimaan;
                    newRow.jumlah_anggaran = penerimaan;
                    newRow.jumlah_realisasi = pengeluaran;
                    newRow.prosentase_pajak = bukuKasUmum.TotalPajakDiterima == 0 ? 0 : (double)bukuKasUmum.TotalPajakDisetor / (double)bukuKasUmum.TotalPajakDiterima;
                    newRow.pajak_yang_disetorkan = bukuKasUmum.TotalPajakDisetor;
                    newRow.pajak_yang_diterima = bukuKasUmum.TotalPajakDiterima;
                    newRow.sisa_dana = penerimaan - pengeluaran;
                    newRow.keterangan = "";
                    laporanTriwulan.AddLaporanTriwulanRow(newRow);
                }
                else
                {
                    SqliteDataManager.LaporanTriwulanRow row = laporanTriwulan[0];
                    row.prosentase_anggaran = penerimaan == 0 ? 0 : (double)pengeluaran / (double)penerimaan;
                    row.jumlah_anggaran = penerimaan;
                    row.jumlah_realisasi = pengeluaran;
                    row.prosentase_pajak = bukuKasUmum.TotalPajakDiterima == 0 ? 0 : (double)bukuKasUmum.TotalPajakDisetor / (double)bukuKasUmum.TotalPajakDiterima;
                    row.pajak_yang_disetorkan = bukuKasUmum.TotalPajakDisetor;
                    row.pajak_yang_diterima = bukuKasUmum.TotalPajakDiterima;
                    row.sisa_dana = penerimaan - pengeluaran;
                }

                return laporanTriwulanTA.Update(laporanTriwulan);
            }
        }

        public void PrintExcel(SqliteDataManager.LaporanTriwulanDataTable bukuKasUmum)
        {
            string fileName = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + ".xlsx";
            WriteToExcel(fileName, bukuKasUmum);

            try
            {
                ProcessStartInfo processInfo = new ProcessStartInfo();
                processInfo.FileName = fileName;
                processInfo.WindowStyle = ProcessWindowStyle.Hidden;
                processInfo.Verb = "Print";
                processInfo.CreateNoWindow = true;
                processInfo.UseShellExecute = true;
                Process process = Process.Start(processInfo);
                if (process != null)
                    process.WaitForExit();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                File.Delete(fileName);
            }
        }

        public void ExportToExcel(string filename, SqliteDataManager.LaporanTriwulanDataTable table)
        {
            WriteToExcel(filename, table);
        }

        public void WriteToExcel(string filename, SqliteDataManager.LaporanTriwulanDataTable table)
        {
            if (table.Count == 0) return;

            SqliteDataManager.ProfilSekolahRow profil = new ProfilBusiness().GetProfil();

            using (ExcelPackage package = new ExcelPackage(new FileInfo(String.Format(@"templates\{0}", "laporan-triwulan.xlsx"))))
            {
                ExcelWorksheet ws = package.Workbook.Worksheets[1];

                int row = 14;
                foreach (SqliteDataManager.LaporanTriwulanRow record in table.Rows)
                {
                    ws.Cells["A" + row].Value = record.no;
                    ws.Cells["B" + row].Value = record.uraian;
                    ws.Cells["C" + row].Value = record.prosentase_anggaran;
                    ws.Cells["D" + row].Value = record.jumlah_anggaran;
                    ws.Cells["E" + row].Value = record.jumlah_realisasi;
                    ws.Cells["F" + row].Value = record.prosentase_pajak;
                    ws.Cells["G" + row].Value = record.pajak_yang_diterima;
                    ws.Cells["H" + row].Value = record.pajak_yang_disetorkan;
                    ws.Cells["I" + row].Value = record.sisa_dana;
                    ws.Cells["J" + row].Value = record.keterangan;
                    
                    row++;
                }

                // nama sekolah
                ws.Cells["C7"].Value = string.Format(": {0}", profil.sekolah);
                // triwulan
                ws.Cells["C8"].Value = string.Format(": {0}", Enums.GetTriwulanByName(table[0].triwulan.ToString()));
                // tahun anggaran
                ws.Cells["C9"].Value = string.Format(": {0}", table[0].tahun);
                // format jumlah, anggaran, realisasi, pajak
                ws.Cells["D14:E14,G14:H14"].Style.Numberformat.Format = "_(Rp* #,##0.00_);_(Rp* (#,##0.00);_(Rp* \" - \"??_);_(@_)";
                // format persentase
                ws.Cells["C14,F14"].Style.Numberformat.Format = "0.00%";
                // tempat, tanggal
                ws.Cells["I17"].Value = string.Format("Jakarta, {0}", DateTime.Now.ToString("dd MMMM yyyy"));
                // ketua yayasan
                ws.Cells["B24"].Value = profil.ketua_yayasan;
                // kepala sekolah
                ws.Cells["E24"].Value = profil.kepala_sekolah;
                // bendahara sekolah
                ws.Cells["I24"].Value = profil.bendahara_sekolah;

                if (!filename.EndsWith("xlsx")) filename += ".xlsx";
                package.SaveAs(new FileInfo(filename));
            }
        }
    }
}
